from time import sleep
import xlrd
import xlwt
import csv
import random
from sqlite_function import *


# 生成一个随机时间

def random_time():
    hour = random.choice(['10', '11', '12', '13', '14', '15', '16'])
    minite = random.choice(['10', '11', '12', '13', '14', '15', '16'])
    second = random.choice(
        ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18',
         '19', '20'])
    time = hour + ':' + minite + ':' + second
    return time


def read(path, bigname):
    data = xlrd.open_workbook(path, 'rb')
    names = data.sheet_names()
    zfh = []
    for i in names:
        table = data.sheet_by_name(i)
        # 默认开始行数
        line = 3
        # 装小类数据的数组
        cmy = []
        # 结束行数
        nrows = table.nrows
        # print('---------')
        # print('小类名称', i, '行数', table.nrows)
        while line <= nrows - 1:
            # print('目前行数', line + 1)
            # 整行跳过判断如果2-7都是空的就直接跳过本行
            hxy = table.cell_value(line, 0)
            if hxy == '':
                line = line + 1
                continue
            a = table.cell_value(line, 2)
            b = table.cell_value(line, 3)
            c = table.cell_value(line, 4)
            d = table.cell_value(line, 5)
            e = table.cell_value(line, 6)
            f = table.cell_value(line, 7)
            # 借方或者贷方哪边的单价总价数量三个位置都有数字，就判定它是哪边的
            if a != '' and b != '' and c != '':
                try:
                    temp1 = float(a)
                    temp1 = float(b)
                    temp1 = float(c)
                except:
                    line = line + 1
                    continue
            elif d != '' and e != '' and f != '':
                try:
                    temp1 = float(d)
                    temp1 = float(e)
                    temp1 = float(f)
                except:
                    line = line + 1
                    continue
            else:
                line = line + 1
                continue

            date = xlrd.xldate.xldate_as_datetime(table.cell_value(line, 0), 0).__format__(r'%Y-%m-%d')
            time = random_time()

            # 日期
            date_time = date + ' ' + time
            # 处理方
            handle = str(table.cell_value(line, 1))
            if handle == '':
                handle = '未知'
            # 判断是入库还是出库
            if d != '' and e != '' and f != '':
                # 说明是出库，生成记录
                temp2 = [date_time, bigname, i, handle, 0-float(d), e, 0-float(f)]
                cmy.append(temp2)
            else:
                temp2 = [date_time, bigname, i, handle, a, b, c]
                cmy.append(temp2)

            line = line + 1
        if cmy != []:
            zfh.append(cmy)
    return zfh


# 插入总表
def write(data):
    big_name = data[-1][-1][1]
    con, cur = db_open()

    # 删除大类表
    temp = sqliteObject_to_list_a(cur, f'''select * from sqlite_master''')
    if big_name in temp:
        sql2 = f'''
            drop table '{big_name}'
        '''
        cur.execute(sql2)
        con.commit()

    # 新建大类表
    sql3 = f'''
        create table '{big_name}'
        (
            date text,
            bigname text,
            smallname text,
            handle text,
            change numeric,
            unitprice numeric,
            price numeric
        );
    '''
    cur.execute(sql3)
    con.commit()

    # 删除大类表中的相关记录和小类表
    # 删除总表的记录
    sql1 = f'''
        delete from stateless_part where bigname = '{big_name}'
    '''
    cur.execute(sql1)
    con.commit()

    # data = [ [[轴承,A100,10,10],[轴承,A100,10,10]], [],[] ]
    # i 指的是大类  i = [[轴承,A100,10,10],[轴承,A100,10,10]]
    for i in data:
        # 计算总表中需要插入的内容
        unitprice = float(i[-1][5])
        num = 0
        for j in i:
            num = num + float(j[4])
        price = unitprice * num
        sql4 = f'''
            insert into stateless_part(
                bigname,
                smallname,
                photo,
                number,
                unitprice,
                total
            )
            values(
                ?,
                ?,
                ?,
                ?,
                ?,
                ?
            )
        '''
        cur.execute(sql4, (i[-1][1], i[-1][2], 'null', num, unitprice, price))
        con.commit()

        # 在大类表中插入内容
        for h in i:
            sql5 = f'''
                insert into '{big_name}'(
                    date,
                    bigname,
                    smallname,
                    handle,
                    change,
                    unitprice,
                    price
                )
                values(
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?
                )
                '''
            cur.execute(sql5, (h[0], h[1], h[2], h[3], h[4], h[5], h[6]))
            con.commit()
    print('数据库写入完成')


# 待录入表
table1 = ['轴承', '油管', '铜阀', '螺丝-平键-天车配件', '快叉-刀头', '角带-链条', '机械加工', '焊接', '电器', '电工附件', '磁棒-锯片']
# table1 = ['磁棒-锯片']
path1 = r'C:\Users\谢佩恒\Desktop\仓库项目\仓库备件记录台账\用导入的记录\\'
path2 = r'.xls'

for i in table1:
    path = path1 + i + path2
    data = read(path, i)
    write(data)
    print(f'{i}全部完成')
